﻿CREATE PROCEDURE [dbo].[books_get_listing]
    @page int = 0,
    @size int = 0,
    @column nvarchar(256),
    @direction int = 0,
    @name nvarchar(256) = null,
    @authorId int = 0,
    @categoryId int = 0
AS

SELECT
    Books.BookId,
    Books.Title AS Book_Title,
    Books.ShortContent AS Book_ShortContent,
    Books.DateOfPublish AS Book_DateOfPublish,
    Books.Avatar AS Book_Image,
    (SELECT (Authors.LastName + ' ' + Authors.FirstName) + ',' AS 'data()'
        FROM    Authors
        WHERE    Authors.AuthorId IN (
            SELECT BooksByAuthor.AuthorId
            FROM BooksByAuthor
            WHERE BooksByAuthor.BookId = Books.BookId
        )
        ORDER BY Authors.LastName
        FOR xml path('')) AS Authors,
    (SELECT Categories.Name + ',' AS 'data()'
        FROM    Categories
        WHERE    Categories.CategoryId IN (
            SELECT BooksByCategory.CategoryId
            FROM BooksByCategory
            WHERE BooksByCategory.BookId = Books.BookId
        )
        ORDER BY Categories.Name
        FOR xml path('')) AS Categories
FROM Books
    inner join BooksByAuthor ON Books.BookId = BooksByAuthor.BookId
    inner join BooksByCategory ON Books.BookId = BooksByCategory.BookId
WHERE
    (@authorId = 0 OR (BooksByAuthor.AuthorId = @authorId)) AND
    (@categoryId = 0 OR (BooksByCategory.CategoryId = @categoryId)) AND
    (@name IS NULL OR Books.Title LIKE '%' + @name+ '%')
GROUP BY Books.BookId, Books.Title, Books.ShortContent, Books.DateOfPublish, Books.Avatar
ORDER BY
    CASE WHEN @column = 'Name' AND @direction = 1 THEN Books.Title END DESC,
    CASE WHEN @column = 'Name' AND @direction = 0 THEN Books.Title END ASC,
    CASE WHEN @column = 'DateOfPublish' AND @direction = 1 THEN Books.DateOfPublish END DESC,
    CASE WHEN @column = 'DateOfPublish' AND @direction = 0 THEN Books.DateOfPublish END ASC,
    CASE WHEN @column = 'Authors' AND @direction = 1 THEN
        (SELECT (Authors.LastName + ' ' + Authors.FirstName) + ','
        FROM    Authors
        WHERE    Authors.AuthorId IN (
            SELECT AuthorId
            FROM BooksByAuthor
            WHERE BooksByAuthor.BookId = Books.BookId
        )
        ORDER BY Authors.LastName
        FOR xml path(''))
    END DESC,
    CASE WHEN @column = 'Authors' AND @direction = 0 THEN 
        (SELECT (Authors.LastName + ' ' + Authors.FirstName) + ','
        FROM    Authors
        WHERE    Authors.AuthorId IN (
            SELECT BooksByAuthor.AuthorId
            FROM BooksByAuthor
            WHERE BooksByAuthor.BookId = Books.BookId
        )
        ORDER BY Authors.LastName
        FOR xml path(''))
    END ASC,
    CASE WHEN @column = 'Categories' AND @direction = 1 THEN
        (SELECT Categories.Name + ',' AS 'data()'
        FROM    Categories
        WHERE    Categories.CategoryId IN (
            SELECT BooksByCategory.CategoryId
            FROM BooksByCategory
            WHERE BooksByCategory.BookId = Books.BookId
        )
        ORDER BY Categories.Name
        FOR xml path(''))
    END DESC,
    CASE WHEN @column = 'Categories' AND @direction = 0 THEN
        (SELECT Categories.Name + ',' AS 'data()'
        FROM    Categories
        WHERE    Categories.CategoryId IN (
            SELECT CategoryId
            FROM BooksByCategory
            WHERE BooksByCategory.BookId = Books.BookId
        )
        ORDER BY Categories.Name
        FOR xml path(''))
    END ASC

OFFSET ((@page-1)*@size) ROWS
FETCH NEXT @size ROWS ONLY;

Declare @TotalCount int
SELECT @TotalCount= Count(DISTINCT Books.BookId)
	FROM Books
		inner join BooksByAuthor ON Books.BookId = BooksByAuthor.BookId
		inner join BooksByCategory ON Books.BookId = BooksByCategory.BookId
	WHERE
		(@authorId = 0 OR (BooksByAuthor.AuthorId = @authorId)) AND
		(@categoryId = 0 OR (BooksByCategory.CategoryId = @categoryId)) AND
		(@name IS NULL OR Books.Title LIKE '%' + @name+ '%')

RETURN @TotalCount;
